﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_FIT_REPORT_Fit2011r_Get_Monthly_Training_Result')
	BEGIN
		DROP Procedure usp_UPDMS_FIT_REPORT_Fit2011r_Get_Monthly_Training_Result
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_FIT_REPORT_Fit2011r_Get_Monthly_Training_Result
**	Desc : 트레이닝캘린더
**	Test Exec Query : Exec usp_UPDMS_FIT_REPORT_Fit2011r_Get_Monthly_Training_Result '2015-02', '', '', 'ko'
**	Called by : Fit_Dac_UPDMS_FIT_WEIGHT_TRAINING.cs
**	Program ID : Fit2011r
**	Auth : 송시명
**	Date : 2014-05-29
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_FIT_REPORT_Fit2011r_Get_Monthly_Training_Result]
@ls_base_ym nvarchar(7),
@ls_training_part nvarchar(max),
@ls_training_type_id nvarchar(max),
@ls_lang_set nvarchar(2)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @training_parts nvarchar(max)
IF @ls_training_part = ''
BEGIN
    SET @training_parts = '%'
END
ELSE
BEGIN
    SET @training_parts = @ls_training_part
END

DECLARE @training_type_ids nvarchar(max)
IF @ls_training_type_id = ''
BEGIN
    SET @training_type_ids = '%'
END
ELSE
BEGIN
    SET @training_type_ids = @ls_training_type_id
END

--캘린더
SELECT ISNULL([일], '') AS Sun,
       ISNULL([월], '') AS Mon,
       ISNULL([화], '') AS Tue,
       ISNULL([수], '') AS Wed,
       ISNULL([목], '') AS Thu,
       ISNULL([금], '') AS Fri,
       ISNULL([토], '') AS Sat
  FROM (
       SELECT z.Day_K_Abb_Nm,
              umd.Week_K_Nm,
              '<span class="' + CASE umd.Holiday_Yn WHEN 1 THEN 'color_red ' ELSE '' END +
              CASE LEFT(CONVERT(VARCHAR, GETDATE(), 121), 10) WHEN umd.Dt THEN 'today ' ELSE '' END + 'dt' +
              --입력된 트레이닝이 없으면 등록화면 링크
              CASE WHEN LEN(z.Contents) < 40 THEN ' hand" onclick="goTrainingMgtPage(''' + umd.Dt + ''')"' ELSE '"' END + '>'
              + RIGHT(umd.Dt, 2) + '</span><br />' + z.Contents AS Contents
         FROM UPDMS_MST_DATE AS umd WITH(NOLOCK)
         LEFT OUTER JOIN
              (
              SELECT DISTINCT Day_K_Abb_Nm,
                     Dt,
                     REPLACE(REPLACE(ISNULL(Contents,''), '&lt;', '<'),'&gt;', '>') AS Contents
                FROM (
                     SELECT umd.Day_K_Abb_Nm,
                            umd.Dt,
                            ( SELECT LTRIM(Contents) + '' AS [text()]
                                FROM (
                                     SELECT DISTINCT
                                            A.Training_Dt,
                                            '<div class="hand training_box training_part_' + C.Training_Part + '" onclick="goWeightTrainingResultPage(''' + A.Training_Id + ''')">●'
                                            + dbo.ufn_UPDMS_Get_Code_Name(C.Training_Part, 'FIT', @ls_lang_set)
                                            + '</div>' AS Contents
                                       FROM UPDMS_FIT_WEIGHT_TRAINING AS A WITH(NOLOCK)
	                                   JOIN UPDMS_FIT_WEIGHT_TRAINING_PARTS AS B WITH(NOLOCK) ON A.Training_Id = B.Training_Id
	                                   JOIN UPDMS_FIT_TRAINING_TYPE AS C WITH(NOLOCK) ON B.Training_Type_Id = C.Training_Type_Id
                                      WHERE LEFT(A.Training_Dt, 7) = @ls_base_ym
                                        AND (C.Training_Part IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@training_parts))
                                            OR C.Training_Part LIKE @training_parts
                                            )
                                        AND (C.Training_Type_Id IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@training_type_ids))
                                            OR C.Training_Type_Id LIKE @training_type_ids
                                            )
                                     ) tmp
                               WHERE tmp.Training_Dt = x.Training_Dt FOR XML PATH('')
                             ) AS Contents
                        FROM UPDMS_MST_DATE AS umd WITH(NOLOCK)
                        LEFT OUTER JOIN
                             (
                             SELECT DISTINCT
                                    A.Training_Dt
                               FROM UPDMS_FIT_WEIGHT_TRAINING AS A WITH(NOLOCK)
	                           JOIN UPDMS_FIT_WEIGHT_TRAINING_PARTS AS B WITH(NOLOCK) ON A.Training_Id = B.Training_Id
	                           JOIN UPDMS_FIT_TRAINING_TYPE AS C WITH(NOLOCK) ON B.Training_Type_Id = C.Training_Type_Id
                              WHERE LEFT(A.Training_Dt, 7) = @ls_base_ym
                                AND (C.Training_Part IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@training_parts))
                                    OR C.Training_Part LIKE @training_parts
                                    )
                                AND (C.Training_Type_Id IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@training_type_ids))
                                    OR C.Training_Type_Id LIKE @training_type_ids
                                    )
                             ) AS x
                          ON umd.Dt = x.Training_Dt
                       WHERE umd.Yyyy_Mm = @ls_base_ym
                   ) AS rslt
              ) AS z
           ON umd.Dt = z.Dt
        WHERE umd.Yyyy_Mm = @ls_base_ym
       ) AS pv
 PIVOT ( MAX(pv.Contents) FOR pv.Day_K_Abb_Nm IN ([일],[월],[화],[수],[목],[금],[토])
       ) AS pvt


--운동부위별 총량 챠트
SELECT ISNULL(y.Training_Ym, @ls_base_ym) AS Training_Ym,
       x.Cd AS Training_Part,
       dbo.ufn_UPDMS_Get_Code_Name(x.Cd, 'FIT', @ls_lang_set) AS Training_Part_Nm,
       ISNULL(y.Total_Weight, 0.00) AS Total_Weight,
       ISNULL(y.Total_Count, 0) AS Total_Count,
	   ISNULL(y.Training_Day, 0) AS Training_Day
  FROM (
       SELECT Cd,
              View_Order
         FROM UPDMS_COM_CD WITH(NOLOCK)
        WHERE Id = 'FIT'
          AND Cd LIKE 'AB%'
          AND Use_Yn = 'Y'
       ) x
  LEFT OUTER JOIN (
       SELECT LEFT(A.Training_Dt, 7) AS Training_Ym,
              C.Training_Part,
              SUM(B.Action_Weight * B.Action_Count * C.Weight_Calc_Rate * B.Set_Count) AS Total_Weight,
              SUM(B.Action_Count * B.Set_Count) AS Total_Count,
              COUNT(DISTINCT A.Training_Id) AS Training_Day
         FROM UPDMS_FIT_WEIGHT_TRAINING AS A WITH(NOLOCK)
         JOIN UPDMS_FIT_WEIGHT_TRAINING_PARTS AS B WITH(NOLOCK) ON A.Training_Id = B.Training_Id
         JOIN UPDMS_FIT_TRAINING_TYPE AS C WITH(NOLOCK) ON B.Training_Type_Id = C.Training_Type_Id
        WHERE LEFT(A.Training_Dt, 7) = @ls_base_ym
          AND (C.Training_Part IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@training_parts))
              OR C.Training_Part LIKE @training_parts
              )
          AND (C.Training_Type_Id IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@training_type_ids))
              OR C.Training_Type_Id LIKE @training_type_ids
              )
        GROUP BY LEFT(A.Training_Dt, 7), C.Training_Part
       ) y
    ON x.Cd = y.Training_Part
 ORDER BY x.View_Order


--총운동일수, 운동시간(분), 평균운동일, 평균운동시간(분)
SELECT COUNT(Z.Training_Id) AS Total_Training_Day,
       COUNT(Z.Dt) AS Total_Day,
       ISNULL(SUM(DATEDIFF(MINUTE,
                           Z.Dt + ' ' + Z.Start_Hour + ':' + Z.Start_Minute,
                           Z.Dt + ' ' + Z.End_Hour + ':' + Z.End_Minute)
                 ), 0) AS Total_Training_Minute,
       (CONVERT(DECIMAL(10,2), COUNT(Z.Training_Id)) / CONVERT(DECIMAL(10,2), COUNT(Z.Dt))) * CONVERT(DECIMAL(10,2), 100) AS Total_Training_Day_Avg,
       ISNULL(SUM(DATEDIFF(MINUTE,
                           Z.Dt + ' ' + Z.Start_Hour + ':' + Z.Start_Minute,
                           Z.Dt + ' ' + Z.End_Hour + ':' + Z.End_Minute)
                 ) / COUNT(Z.Training_Id), 0) AS Total_Training_Minute_Avg
  FROM (
       SELECT DISTINCT X.Dt,
              Y.Training_Id,
              Y.Start_Hour,
              Y.Start_Minute,
              Y.End_Hour,
              Y.End_Minute
         FROM UPDMS_MST_DATE X WITH(NOLOCK)
         LEFT OUTER JOIN (
              SELECT A.Training_Dt,
                     A.Training_Id,
                     A.Start_Hour,
                     A.Start_Minute,
                     A.End_Hour,
                     A.End_Minute
                FROM UPDMS_FIT_WEIGHT_TRAINING AS A WITH(NOLOCK)
                JOIN UPDMS_FIT_WEIGHT_TRAINING_PARTS AS B WITH(NOLOCK) ON A.Training_Id = B.Training_Id
                JOIN UPDMS_FIT_TRAINING_TYPE AS C WITH(NOLOCK) ON B.Training_Type_Id = C.Training_Type_Id
               WHERE LEFT(A.Training_Dt, 7) = @ls_base_ym
                 AND (C.Training_Part IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@training_parts))
                     OR C.Training_Part LIKE @training_parts
                     )
                 AND (C.Training_Type_Id IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@training_type_ids))
                     OR C.Training_Type_Id LIKE @training_type_ids
                     )
              ) Y
           ON X.Dt = Y.Training_Dt
        WHERE X.Yyyy_Mm = @ls_base_ym
       ) Z

GO